from sqlalchemy import select
from sqlalchemy import text
from sqlalchemy.orm import Session

from api.fm.models.system.config_model import User

from tests.test_base import box

db_manager = box.db_manager()

with db_manager.get_db_connection() as connection:
    statement = text("SELECT * FROM users")
    print(connection.execute(statement).mappings().all())

with db_manager.get_db_session() as session:
    statement = text("SELECT * FROM users")
    res = session.execute(statement)
    print(res.mappings().all())

with db_manager.get_db_session() as session:
    print(session.query(User).filter(User.id == 1).all())

with db_manager.get_db_session() as session:   # type: Session
    stmt = select(User).where(User.id == 1)
    print(session.execute(stmt).scalars().all())

# 增
with db_manager.get_db_session() as session:
    new_user = User(username="Alice", email="alice@example.com")
    session.add(new_user)
    session.commit()
    print("User added:", new_user.id)

# 查
with db_manager.get_db_session() as session:
    stmt = select(User).where(User.username == "Alice")
    user = session.execute(stmt).scalars().first()
    if user:
        print("User found:", user.serialize)
    else:
        print("User not found")

# 更新用户
with db_manager.get_db_session() as session:
    stmt = select(User.email).where(User.username == "Alice")
    user_to_update = session.execute(stmt).scalars().first()
    if user_to_update:
        user_to_update.email = "alice_new@example.com"
        session.commit()
        print("User updated:", user_to_update.id, user_to_update.username, user_to_update.email)
    else:
        print("User not found")

# 删除用户
with db_manager.get_db_session() as session:
    stmt = select(User.username).where(User.username == "Alice")
    user_to_delete = session.execute(stmt).scalars().first()
    if user_to_delete:
        session.delete(user_to_delete)
        session.commit()
        print("User deleted")
    else:
        print("User not found")
